﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace BookStore.Admin.Models
{
    public class BillDetailModel
    {
        private string connectionString;
        private SqlConnection connect;

        public BillDetailModel()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            connect = new SqlConnection(connectionString);
        }

        public int deleteBillDetail(int id)
        {
            try
            {
                connect.Open();
                string sqlquery = "DELETE FROM Bill_detail WHERE bill_id=" + id;
                SqlCommand scm = new SqlCommand(sqlquery, connect);
                scm.ExecuteNonQuery();
                
            }
            catch (Exception sl)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }
            return 0;
        }
        public string[][] getBillDetailByBillId(int id)
        {
            string [][]result = new string[100][];
            try
            {
                connect.Open();
                string update = "";
                string sqlquery_1 = "select updated_date FROM Bill WHERE bill_id=" + id;
                SqlCommand scm_1 = new SqlCommand(sqlquery_1, connect);
                SqlDataReader reader_1 = scm_1.ExecuteReader();

                if (reader_1.Read())
                {
                    update = reader_1["updated_date"].ToString();
                }
                reader_1.Close();           
                string sqlquery = " SELECT * FROM Bill_detail WHERE bill_id =  " + id;
                SqlCommand scm = new SqlCommand(sqlquery, connect);
                SqlDataReader reader = scm.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {
                    result[i] = new string[5];
                    result[i][0] = reader["book_name"].ToString();
                    result[i][1] = reader["price"].ToString();
                    result[i][2] = reader["quantity"].ToString();
                    result[i][3] = reader["total_price"].ToString();
                    result[i][4] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(update)).ToString("dd-MM-yyyy");
                    i++;
                }
            }
            catch (Exception se)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }
            return result;
        }
        public string[][] getBillDetailByBillId_1(int id)
        {
            string[][] result = new string[100][];
            try
            {
                connect.Open();
                string sqlquery = " SELECT * FROM Bill_detail WHERE bill_id =  " + id;
                SqlCommand scm = new SqlCommand(sqlquery, connect);
                SqlDataReader reader = scm.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {
                    result[i] = new string[3];
                    result[i][0] = reader["book_id"].ToString();
                    result[i][1] = reader["quantity"].ToString();
                    i++;
                }
            }
            catch (Exception se)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }
            return result;
        }


    }
}
